SELECT BRANCH_CODE_SME
		,BRANCH_NAME_SME
		,DAO_NAME, DAO 
		,CIF
		,REGION
		,CUS_NAME
		,INDUSTRY_NAME
		,INDUSTRY_NAME_EN
		,'' AS LOAN_MAT
		,'' AS DEPOSIT_MAT
		,'' AS BL_MAT
		,'' AS LIMIT_MAT
		,'' AS LOAN_OVERDUE
		,'' AS CARD_OVERDUE
		,'' AS COMP_CEL
		,'' AS REPR_CEL
		,'' AS CASA_DESC
		,'' AS DORMANT_WARN
 FROM TBL_CM_ALERT_LIMIT

 DROP TABLE TBL_GENERAL_ALERT_RAW

 SELECT * 
 INTO TBL_GENERAL_ALERT_RAW
 FROM
 (
 SELECT BRANCH_ID,DAO_NAME COLLATE DATABASE_DEFAULT AS DAO_NAME,DAO,CIF,CUSTOMER_NAME,'LOAN_MAT' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_ALERT_LOAN_MATDT
UNION ALL 
SELECT BRANCH_CODE,DAO_NAME,DAO ,CIF,CUSTOMER_NAME,'DEPOSIT_MAT' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_ALERT_DEPOSIT_MATDT
UNION ALL 
SELECT BRANCH_ID,DAO_NAME,DAO_CIF ,CIF,CUS_NAME,'BL_MAT' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_CM_ALERT_BL
UNION ALL 
SELECT BRANCH_ID,DAO_NAME,DAO,CIF,CUS_NAME,'LIMIT_MAT' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_CM_ALERT_LIMIT
UNION ALL 
SELECT B.BRANCH_ID,DAO_NAME,DAO,CIF,CUS_NAME,'LOAN_OVERDUE' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_CM_ALERT_OVERDUE A, TBL_BRANCH B WHERE A.BRANCH_CODE_SME = B.BRANCH_CODE_SME
UNION ALL 
SELECT BRANCH_ID,DAO_NAME,DAO,CONTRACT_CIF,CUS_NAME,'CARD_OVERDUE' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_ALERT_CARD_OVERDUE 
UNION ALL 
SELECT BRANCH_ID,DAO_NAME,DAO COLLATE DATABASE_DEFAULT,CIF,CUS_NAME,'CASA_DESC' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_ALERT_CURRENT_ACCOUNT WHERE DELTA_2 < -5*1E6 AND DELTA_3 < -5*1E6 AND DELTA_4 < -5*1E6
UNION ALL 
SELECT BRANCH_ID,DAO_NAME,DAO,CIF,CUS_NAME,'DORMANT_WARN' AS TYPE_ALERT, 'X' AS HAVE_LIST
		FROM TBL_CM_ALERT_INACTIVE ) X
		

		select * from TBL_GENERAL_ALERT_RAW where CIF = '1770948'

		
SELECT *
INTO TBL_GENERAL_ALERT
FROM 
(SELECT *
FROM TBL_GENERAL_ALERT_RAW ) AS A
PIVOT
(MAX(HAVE_LIST) FOR TYPE_ALERT IN ([LOAN_MAT],[DEPOSIT_MAT],[LIMIT_MAT],[LOAN_OVERDUE],[DORMANT_WARN],[BL_MAT],[CASA_DESC],[CARD_OVERDUE])) AS PVT


select distinct type_alert from TBL_GENERAL_ALERT_RAW

delete from TBL_GENERAL_ALERT where dao is null
select * from TBL_GENERAL_ALERT where dao is null
order by cif